package com.apes.scm.masterdata.product.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.Arrays;

@Component
public class CategoryDao extends ApesDao {

    /***
     * 临时导入数据专用
     */
    public JSONArray queryCategoryTagTemp(){
        String sSQL = "SELECT T.NAME, T.CATEGORY_ID FROM MAS_CATEGORY_TAG_TEMP  T WHERE 1 = 1";

        return toJSONArray(find(sSQL));
    }

    public JSONArray queryCategoryWorkHours(String s){
        JSONObject param = new JSONObject();
        param.put("CATEGORY_ID",s);
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT * ");
        sf.append("(SELECT * FROM MAS_PRODUCT Z WHERE Z.ID = A.PRODUCT_ID)");
        sf.append("FROM MAS_CATEGORY_WORK_HOURS A ");
        sf.append("WHERE A.CATEGORY_ID = :CATEGORY_ID");
        return toJSONArray(find(sf.toString(), param));
    }

    /***
     * 混合查询
     */
    public JSONArray queryCategory(JSONObject param) {
        param.put("QUERY_STRING", packageQueryString(param.getString("KEYWORD")));
        String sSQL = "SELECT G.*\n" +
                "  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE\n" +
                "           FROM (SELECT MAX(ROWNUM) OVER() TOTAL, CATEGORY_ID, NAME, TAG_NAME\n" +
                "                    FROM (SELECT A.ID CATEGORY_ID, A.NAME, A.NAME TAG_NAME\n" +
                "                             FROM MAS_CATEGORY A\n" +
                "                            WHERE A.MATCH_PRODUCT = 1\n" +
                "                           UNION ALL\n" +
                "                           SELECT A.ID CATEGORY_ID, A.NAME, B.NAME TAG_NAME\n" +
                "                             FROM MAS_CATEGORY A, MAS_CATEGORY_TAG B\n" +
                "                            WHERE A.ID = B.CATEGORY_ID\n" +
                "                              AND A.MATCH_PRODUCT = 1\n" +
                "                              AND A.NAME <> B.NAME)\n" +
                "                   WHERE TAG_NAME LIKE :QUERY_STRING OR NAME LIKE :QUERY_STRING OR CATEGORY_ID LIKE :QUERY_STRING) G\n" +
                "          WHERE ROWNUM <= DECODE(0, :PAGE, :SIZE, (:PAGE + 1) * :SIZE)) G\n" +
                " WHERE RN > (:PAGE) * :SIZE";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 组装查询条件
     */

    private String packageQueryString(String keyword) {
        String[] strs = keyword.split("");
        String queryString = Arrays.stream(strs).reduce("",(a, b) -> a + "%" + b,String::concat);
        return queryString + "%";
    }


    /***
     * 混合查询
     */
    public JSONArray findAccessoryByKeyword(JSONObject param) {
        param.put("QUERY_STRING", packageQueryString(param.getString("KEYWORD")));
        String sSQL = "SELECT G.*\n" +
                "  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE\n" +
                "           FROM (SELECT MAX(ROWNUM) OVER() TOTAL, CATEGORY_ID, NAME\n" +
                "                FROM ( " +
                "select CATEGORY_ID, NAME from ( " +
                "                   SELECT CATEGORY_ID, NAME, TAG_NAME FROM (" +
                "                       SELECT A.ID CATEGORY_ID, A.NAME, A.NAME TAG_NAME\n" +
                "                             FROM MAS_CATEGORY A\n" +
                "                            WHERE A.MATCH_PRODUCT = 1\n" +
                "                           UNION ALL\n" +
                "                           SELECT A.ID CATEGORY_ID, A.NAME, B.NAME TAG_NAME\n" +
                "                             FROM MAS_CATEGORY A, MAS_CATEGORY_TAG B\n" +
                "                            WHERE A.ID = B.CATEGORY_ID\n" +
                "                              AND A.MATCH_PRODUCT = 1\n" +
                "                              AND A.NAME <> B.NAME)\n" +
                "                   WHERE TAG_NAME LIKE :QUERY_STRING OR NAME LIKE :QUERY_STRING OR CATEGORY_ID LIKE :QUERY_STRING" +
                ")" +
                " WHERE NAME = '" + param.getString("KEYWORD") +"'" +
                " group by  CATEGORY_ID, NAME " +
                " UNION ALL "+
                "select CATEGORY_ID, NAME from ( " +
                "                   SELECT CATEGORY_ID, NAME, TAG_NAME FROM (" +
                "                       SELECT A.ID CATEGORY_ID, A.NAME, A.NAME TAG_NAME\n" +
                "                             FROM MAS_CATEGORY A\n" +
                "                            WHERE A.MATCH_PRODUCT = 1\n" +
                "                           UNION ALL\n" +
                "                           SELECT A.ID CATEGORY_ID, A.NAME, B.NAME TAG_NAME\n" +
                "                             FROM MAS_CATEGORY A, MAS_CATEGORY_TAG B\n" +
                "                            WHERE A.ID = B.CATEGORY_ID\n" +
                "                              AND A.MATCH_PRODUCT = 1\n" +
                "                              AND A.NAME <> B.NAME)\n" +
                "                   WHERE TAG_NAME LIKE :QUERY_STRING OR NAME LIKE :QUERY_STRING OR CATEGORY_ID LIKE :QUERY_STRING" +
                ")" +
                " WHERE NAME LIKE '" + param.getString("KEYWORD") +"%'" +
                " AND NAME <> '" + param.getString("KEYWORD") + "'" +
                " group by  CATEGORY_ID, NAME " +
                " UNION ALL "+
                "select CATEGORY_ID, NAME from ( " +
                "                   SELECT CATEGORY_ID, NAME, TAG_NAME FROM (" +
                        "                       SELECT A.ID CATEGORY_ID, A.NAME, A.NAME TAG_NAME\n" +
                        "                             FROM MAS_CATEGORY A\n" +
                        "                            WHERE A.MATCH_PRODUCT = 1\n" +
                        "                           UNION ALL\n" +
                        "                           SELECT A.ID CATEGORY_ID, A.NAME, B.NAME TAG_NAME\n" +
                        "                             FROM MAS_CATEGORY A, MAS_CATEGORY_TAG B\n" +
                        "                            WHERE A.ID = B.CATEGORY_ID\n" +
                        "                              AND A.MATCH_PRODUCT = 1\n" +
                        "                              AND A.NAME <> B.NAME)\n" +
                        "                   WHERE TAG_NAME LIKE :QUERY_STRING OR NAME LIKE :QUERY_STRING OR CATEGORY_ID LIKE :QUERY_STRING" +
                        ")" +
                        " WHERE NAME NOT LIKE '" + param.getString("KEYWORD") +"%' "+
                " AND NAME <> '" + param.getString("KEYWORD") + "'" +
                "group by  CATEGORY_ID, NAME ) ) G\n" +
                "          WHERE ROWNUM <= DECODE(0, :PAGE, :SIZE, (:PAGE + 1) * :SIZE)) G\n" +
                " WHERE RN > (:PAGE) * :SIZE";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryProductByFilter(JSONObject param) {
        String sSql = " SELECT :COLUMN_NAME " ;
        if (param.get("filterShowName") != null) {
            sSql += ", " + param.getString("filterShowName");
        }
        sSql += " FROM MAS_SPXX A WHERE A.PLID = :CLASSIFY_ID " +
                " AND SPZT = 0 \n" +
                " GROUP BY :COLUMN_NAME \n" ;
        if (param.get("filterShowName") != null) {
            sSql += ", " + param.getString("filterShowName");
        }
            sSql += " ORDER BY COUNT(*) DESC";
        return toJSONArray(find(sSql, param));
    }

    public String getCategoryId(String name){
        String sql = "SELECT PJMCID FROM MAS_CATEGORY_CONTRAST WHERE SFPJMC = :name";
        JSONObject param = new JSONObject();
        param.put("name", name);
        JSONArray array = toJSONArray(find(sql, param));
        if (array.isEmpty()) return "";
        return array.getJSONObject(0).getString("PJMCID");
    }
}
